Homework 5 - Documenting an HR database

Thank you for hiring me to take on this role of working directly on you database! After having received credentials to the HR database, I am ready to start revising it to better fit our needs! In this report, I am going to create:

1. A general written description of contents of database.

2. A Chen diagram highlighting the major entities and relations.

3. A Crows-foot diagram highlighting the entities, attributes, and the cardinality and participation of the relations.

4. A python-generated listing of SQL tables and fields, and a listing of sample data showing the first bunch (5 or 10 or so) of EACH table.

5. A HTML report, which will be uploaded to canvas.

Github repo: https://github.com/cmsc-vcu/cmsc408-sp2025-hw5-serrotrehpotsirhc.git

Database design

The following sections document the general design of the HR database.

General description

This database represents the Human Resources of the fortune company I have been hired by! There are multiple sections of data for each employee managed by HR. These sections include:

1. Countries
2. Departments
3. Employees
4. Emp_details_view
5. Jobs
6. Job_grades
7. Job_history
8. Locations
9. Regions.

The purpose of this database is to manage each employee and their relevant data. The major tables are listed above.

Employee-Related Relationships

Employees belong to departments.

Employees have job titles.

Employees have managers (who are also employees).

Employees have a salary and some have commission percentages.

Employees have a work history, including past positions and departments.

Department-Related Relationships

Departments have managers (who are employees).

Departments are associated with specific locations.

Location-Related Relationships

Locations are in cities and countries.

Countries belong to regions.

Job-Related Relationships

Jobs have specific salary ranges (minimum and maximum).

Jobs are categorized into grade levels based on salary ranges.

Hierarchical Relationships

Regions contain countries.

Countries contain locations.

Locations house departments.

Departments employ workers.

Employees have managers, creating a reporting structure.

The purpose of this database is to manage its employees and store their relevant data.

Employee-Related Tables

Employees Table

Stores data of employees, including:

1. Employee ID

2. First and last name

3. Email and phone number

4. Hire date

5. Job ID

6. Salary and commission percentage

7. Manager ID

8. Department ID

Dependents Table

Stores information about employees’ dependents

Job-Related Tables

Jobs Table

Stores job data, including:

1. Job ID

2. Job title

3. Minimum and maximum salary for each position

4. Job History Table

5. Records employees’ job history, including:

6. Employee ID

7. Start and end dates

8. Job ID

9. Department ID

10. Job Grades Table

11. Contains information about salary grades:

12. Grade level

13. Lowest and highest salary for each grade

Organizational Structure Tables

Departments Table

Stores department data:

1. Department ID

2. Department name

3. Manager ID

4. Location ID

Locations Table

Stores the location of company departments:

1. Location ID

2. Street address

3. Postal code

4. City

5. State/Province

6. Country ID

Countries Table

Stores data of countries where the company operates:

1. Country ID

2. Country name

3. Region ID

Regions Table

Stores data of regions:

1. Region ID

2. Region name

Chen diagram

The following diagram highlights the major relations in the database.

graph ER {
    fontname="Helvetica,Arial,sans-serif"
    fontsize=30;
    bgcolor=transparent;

    layout=neato  
    scale=1.2     

    node [fontname="Helvetica,Arial,sans-serif",fontsize=10]

    // Define nodes
    node [shape=box];
    employee; department; job; job_history; job_grade; location; country; region;
    
    node [shape=ellipse];
    employee_id; first_name; last_name; email; phone_number; hire_date; job_id; salary; commission_pct; manager_id; department_id;
    department_id; department_name; location_id; manager_id;
    job_id; job_title; min_salary; max_salary;
    grade_level; lowest_sal; highest_sal;
    location_id; street_address; postal_code; city; state_province; country_id;
    country_id; country_name; region_id;
    region_id; region_name;

    
    node [shape=diamond,style=filled,color=lightgrey]; 
    "Works_In"; "Located_In"; "Part_Of"; "Has_Job"; "History"; "Graded";

    // Define edges
    employee -- employee_id;
    employee -- first_name;
    employee -- last_name;
    employee -- email;
    employee -- phone_number;
    employee -- hire_date;
    employee -- job_id;
    employee -- salary;
    employee -- commission_pct;
    employee -- manager_id;
    employee -- department_id;
    employee -- "Works_In" [label="m"];
    "Works_In" -- department [label="1"];

    department -- department_id;
    department -- department_name;
    department -- location_id;
    department -- manager_id;
    department -- "Located_In" [label="1"];
    "Located_In" -- location [label="1"];

    location -- location_id;
    location -- street_address;
    location -- postal_code;
    location -- city;
    location -- state_province;
    location -- country_id;
    location -- "Part_Of" [label="1"];
    "Part_Of" -- country [label="1"];

    country -- country_id;
    country -- country_name;
    country -- region_id;

    country -- "Part_Of" [label="1"];
    "Part_Of" -- region [label="1"];
    
    region -- region_id;
    region -- region_name;

    employee -- "Has_Job" [label="1"];
    "Has_Job" -- job [label="1"];

    job -- job_id;
    job -- job_title;
    job -- min_salary;
    job -- max_salary;
    job -- "Graded" [label="1"];
    "Graded" -- job_grade [label="1"];

    job_grade -- grade_level;
    job_grade -- lowest_sal;
    job_grade -- highest_sal;

    employee -- "History" [label="m"];
    "History" -- job_history [label="1"];

    job_history -- job_id;
    job_history -- department_id;
    job_history -- employee_id;
    job_history -- start_date;
    job_history -- end_date;
}

Crows foot diagram

The following diagram provides greater detail on the entities and attributes and highlights the cardinality and participation of the relations.

erDiagram
    EMPLOYEE {
        int employeeID
        string first_name
        string last_name
        string email
        string phone_number
        date hire_date
        int salary
        float commission_pct
    }
    
    DEPARTMENT {
        int departmentID
        string department_name
    }
    
    JOB {
        int jobID
        string job_title
        int min_salary
        int max_salary
    }
    
    JOB_GRADE {
        string job_grade_level
        int lowest_sal
        int highest_sal
    }
    
    JOB_HISTORY {
        int jobID
        int departmentID
        date start_date
        date end_date
    }
    
    LOCATION {
        int locationID
        string street_address
        string postal_code
        string city
        string state_province
    }
    
    COUNTRY {
        string countryID
        string country_name
    }
    
    REGION {
        int regionID
        string region_name
    }
    
    EMPLOYEE ||--o{ DEPARTMENT : works_in
    DEPARTMENT ||--o{ LOCATION : located_in
    LOCATION ||--o{ COUNTRY : part_of
    COUNTRY ||--o{ REGION : part_of
    EMPLOYEE ||--|| JOB : has
    JOB ||--o{ JOB_GRADE : graded
    EMPLOYEE ||--o{ JOB_HISTORY : history

erDiagram
    EMPLOYEE {
        int employeeID
        string first_name
        string last_name
        string email
        string phone_number
        date hire_date
        int salary
        float commission_pct
    }
    
    DEPARTMENT {
        int departmentID
        string department_name
    }
    
    JOB {
        int jobID
        string job_title
        int min_salary
        int max_salary
    }
    
    JOB_GRADE {
        string job_grade_level
        int lowest_sal
        int highest_sal
    }
    
    JOB_HISTORY {
        int jobID
        int departmentID
        date start_date
        date end_date
    }
    
    LOCATION {
        int locationID
        string street_address
        string postal_code
        string city
        string state_province
    }
    
    COUNTRY {
        string countryID
        string country_name
    }
    
    REGION {
        int regionID
        string region_name
    }
    
    EMPLOYEE ||--o{ DEPARTMENT : works_in
    DEPARTMENT ||--o{ LOCATION : located_in
    LOCATION ||--o{ COUNTRY : part_of
    COUNTRY ||--o{ REGION : part_of
    EMPLOYEE ||--|| JOB : has
    JOB ||--o{ JOB_GRADE : graded
    EMPLOYEE ||--o{ JOB_HISTORY : history

Listing of tables in the database

The following python blocks highlight the steps used to document the tables in the database. The code is left in the report to show how fun it was to write python!

Create connection

Creating a connection to the “HR” database. This section of code:

1. Interprets the .env file to locate each key
2. Imports the necessary helper packages
3. Saves each of the keys to the config_map
4. Uses the config_map as a key to set up the server using its 4 variables

Feel free to explore the python in the helpers.py file.

from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper
# load credentials from .env file
# load_dotenv()
# Load these variables from first .env file found.
config_map = {
  'user': "CMSC408_HW5_USER",
  'password': "CMSC408_HW5_PASSWORD",
  'host': "CMSC408_HW5_HOST",
  'database': "CMSC408_HW5_DB_NAME"
}

cnx, config = create_db_wrapper( config_map )

List of tables found in HR database

We took this piece of code from explore.qmd, which selects the table name and type using SQL from the information schema tables of our HR database. Printing each table!

run_sql_and_return_df(cnx,"""
SELECT 
  TABLE_NAME, TABLE_TYPE
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_SCHEMA = 'sp25_torresc6_hr'
ORDER BY
  TABLE_NAME
""")

	TABLE_NAME	TABLE_TYPE
0	countries	BASE TABLE
1	departments	BASE TABLE
2	emp_details_view	VIEW
3	employees	BASE TABLE
4	job_grades	BASE TABLE
5	job_history	BASE TABLE
6	jobs	BASE TABLE
7	locations	BASE TABLE
8	regions	BASE TABLE

Listing of Tables and sample data

The following sections provide a high-level view of the data found in the HR database.

Here, we not only print the tables, we also print their data! Similar to the code block above, we select table name, but this time we also select position, column name, and column type! This will help us display not only each table, but alos each column or subcategory as well!

For example, the Countries table has the ids of: country_id, country_name, and region_id, each with separate data under their respective columns! This code prints these column names!

run_sql_and_return_html(cnx,f"""
SELECT 
  TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  1=1
  AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
  TABLE_NAME, ORDINAL_POSITION
""")

TABLE_NAME	ORDINAL_POSITION	COLUMN_NAME	COLUMN_TYPE
countries	1	country_id	char(2)
countries	2	country_name	varchar(40)
countries	3	region_id	int
departments	1	department_id	int
departments	2	department_name	varchar(30)
departments	3	manager_id	int
departments	4	location_id	int
emp_details_view	1	employee_id	int
emp_details_view	2	job_id	varchar(10)
emp_details_view	3	manager_id	int
emp_details_view	4	department_id	int
emp_details_view	5	location_id	int
emp_details_view	6	country_id	char(2)
emp_details_view	7	first_name	varchar(20)
emp_details_view	8	last_name	varchar(25)
emp_details_view	9	salary	decimal(8,2)
emp_details_view	10	commission_pct	decimal(2,2)
emp_details_view	11	department_name	varchar(30)
emp_details_view	12	job_title	varchar(35)
emp_details_view	13	city	varchar(30)
emp_details_view	14	state_province	varchar(25)
emp_details_view	15	country_name	varchar(40)
emp_details_view	16	region_name	varchar(25)
employees	1	employee_id	int
employees	2	first_name	varchar(20)
employees	3	last_name	varchar(25)
employees	4	email	varchar(25)
employees	5	phone_number	varchar(20)
employees	6	hire_date	date
employees	7	job_id	varchar(10)
employees	8	salary	decimal(8,2)
employees	9	commission_pct	decimal(2,2)
employees	10	manager_id	int
employees	11	department_id	int
job_grades	1	grade_level	varchar(3)
job_grades	2	lowest_sal	float
job_grades	3	highest_sal	float
job_history	1	employee_id	int
job_history	2	start_date	date
job_history	3	end_date	date
job_history	4	job_id	varchar(10)
job_history	5	department_id	int
jobs	1	job_id	varchar(10)
jobs	2	job_title	varchar(35)
jobs	3	min_salary	float
jobs	4	max_salary	float
locations	1	location_id	int
locations	2	street_address	varchar(40)
locations	3	postal_code	varchar(12)
locations	4	city	varchar(30)
locations	5	state_province	varchar(25)
locations	6	country_id	char(2)
regions	1	region_id	int
regions	2	region_name	varchar(25)
Total Rows: 54, Total Columns: 4

run_sql_and_return_df(cnx,f"""
SELECT 
  TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  1=1
  AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
  TABLE_NAME, ORDINAL_POSITION
""")

	TABLE_NAME	ORDINAL_POSITION	COLUMN_NAME	COLUMN_TYPE
0	countries	1	country_id	char(2)
1	countries	2	country_name	varchar(40)
2	countries	3	region_id	int
3	departments	1	department_id	int
4	departments	2	department_name	varchar(30)
5	departments	3	manager_id	int
6	departments	4	location_id	int
7	emp_details_view	1	employee_id	int
8	emp_details_view	2	job_id	varchar(10)
9	emp_details_view	3	manager_id	int
10	emp_details_view	4	department_id	int
11	emp_details_view	5	location_id	int
12	emp_details_view	6	country_id	char(2)
13	emp_details_view	7	first_name	varchar(20)
14	emp_details_view	8	last_name	varchar(25)
15	emp_details_view	9	salary	decimal(8,2)
16	emp_details_view	10	commission_pct	decimal(2,2)
17	emp_details_view	11	department_name	varchar(30)
18	emp_details_view	12	job_title	varchar(35)
19	emp_details_view	13	city	varchar(30)
20	emp_details_view	14	state_province	varchar(25)
21	emp_details_view	15	country_name	varchar(40)
22	emp_details_view	16	region_name	varchar(25)
23	employees	1	employee_id	int
24	employees	2	first_name	varchar(20)
25	employees	3	last_name	varchar(25)
26	employees	4	email	varchar(25)
27	employees	5	phone_number	varchar(20)
28	employees	6	hire_date	date
29	employees	7	job_id	varchar(10)
30	employees	8	salary	decimal(8,2)
31	employees	9	commission_pct	decimal(2,2)
32	employees	10	manager_id	int
33	employees	11	department_id	int
34	job_grades	1	grade_level	varchar(3)
35	job_grades	2	lowest_sal	float
36	job_grades	3	highest_sal	float
37	job_history	1	employee_id	int
38	job_history	2	start_date	date
39	job_history	3	end_date	date
40	job_history	4	job_id	varchar(10)
41	job_history	5	department_id	int
42	jobs	1	job_id	varchar(10)
43	jobs	2	job_title	varchar(35)
44	jobs	3	min_salary	float
45	jobs	4	max_salary	float
46	locations	1	location_id	int
47	locations	2	street_address	varchar(40)
48	locations	3	postal_code	varchar(12)
49	locations	4	city	varchar(30)
50	locations	5	state_province	varchar(25)
51	locations	6	country_id	char(2)
52	regions	1	region_id	int
53	regions	2	region_name	varchar(25)

Table: countries

The counties table contains data about the locations of the various offices and departments of the company.

Schema: countries

run_sql_and_return_html(cnx,f"""
SELECT 
        COLUMN_NAME, 
        DATA_TYPE, 
        CHARACTER_MAXIMUM_LENGTH, 
        IS_NULLABLE,
        COLUMN_KEY
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'countries' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
country_id	char	2.0	NO	PRI
country_name	varchar	40.0	YES
region_id	int	NULL	YES	MUL
Total Rows: 3, Total Columns: 5

Sample data: countries

run_sql_and_return_html(cnx,f"""
SELECT
        country_id,
        country_name,
        region_id
    FROM
        countries;
""")

country_id	country_name	region_id
AR	Argentina	2
AU	Australia	3
BE	Belgium	1
BR	Brazil	2
CA	Canada	2
CH	Switzerland	1
CN	China	3
DE	Germany	1
DK	Denmark	1
EG	Egypt	4
FR	France	1
IL	Israel	4
IN	India	3
IT	Italy	1
JP	Japan	3
KW	Kuwait	4
ML	Malaysia	3
MX	Mexico	2
NG	Nigeria	4
NL	Netherlands	1
SG	Singapore	3
UK	United Kingdom	1
US	United States of America	2
ZM	Zambia	4
ZW	Zimbabwe	4
Total Rows: 25, Total Columns: 3

Table: departments

The departments table contains …

Schema: departments

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'departments' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
department_id	int	NULL	NO	PRI
department_name	varchar	30.0	NO	MUL
manager_id	int	NULL	YES	MUL
location_id	int	NULL	YES	MUL
Total Rows: 4, Total Columns: 5

Sample data: departments

run_sql_and_return_html(cnx,f"""
SELECT 

department_id, department_name, manager_id, location_id 

FROM

departments;
""")

department_id	department_name	manager_id	location_id
10	Administration	200.0	1700
20	Marketing	201.0	1800
30	Purchasing	114.0	1700
40	Human Resources	203.0	2400
50	Shipping	121.0	1500
60	IT	103.0	1400
70	Public Relations	204.0	2700
80	Sales	145.0	2500
90	Executive	100.0	1700
100	Finance	108.0	1700
110	Accounting	205.0	1700
120	Treasury	NULL	1700
130	Corporate Tax	NULL	1700
140	Control And Credit	NULL	1700
150	Shareholder Services	NULL	1700
160	Benefits	NULL	1700
170	Manufacturing	NULL	1700
180	Construction	NULL	1700
190	Contracting	NULL	1700
200	Operations	NULL	1700
210	IT Support	NULL	1700
220	NOC	NULL	1700
230	IT Helpdesk	NULL	1700
240	Government Sales	NULL	1700
250	Retail Sales	NULL	1700
260	Recruiting	NULL	1700
270	Payroll	NULL	1700
Total Rows: 27, Total Columns: 4

Table: employees

The employees table contains …

Schema: employees

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
employee_id	int	NULL	NO	PRI
first_name	varchar	20.0	YES
last_name	varchar	25.0	NO	MUL
email	varchar	25.0	NO	UNI
phone_number	varchar	20.0	YES
hire_date	date	NULL	NO
job_id	varchar	10.0	NO	MUL
salary	decimal	NULL	YES
commission_pct	decimal	NULL	YES
manager_id	int	NULL	YES	MUL
department_id	int	NULL	YES	MUL
Total Rows: 11, Total Columns: 5

Sample data: employees

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 

FROM employees;
""")

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.0	NULL	NULL	90.0
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.0	NULL	100.0	90.0
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.0	NULL	100.0	90.0
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.0	NULL	102.0	60.0
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.0	NULL	103.0	60.0
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.0	NULL	103.0	60.0
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.0	NULL	103.0	60.0
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.0	NULL	103.0	60.0
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17	FI_MGR	12008.0	NULL	101.0	100.0
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.0	NULL	108.0	100.0
110	John	Chen	JCHEN	515.124.4269	2005-09-28	FI_ACCOUNT	8200.0	NULL	108.0	100.0
111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30	FI_ACCOUNT	7700.0	NULL	108.0	100.0
112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07	FI_ACCOUNT	7800.0	NULL	108.0	100.0
113	Luis	Popp	LPOPP	515.124.4567	2007-12-07	FI_ACCOUNT	6900.0	NULL	108.0	100.0
114	Den	Raphaely	DRAPHEAL	515.127.4561	2002-12-07	PU_MAN	11000.0	NULL	100.0	30.0
115	Alexander	Khoo	AKHOO	515.127.4562	2003-05-18	PU_CLERK	3100.0	NULL	114.0	30.0
116	Shelli	Baida	SBAIDA	515.127.4563	2005-12-24	PU_CLERK	2900.0	NULL	114.0	30.0
117	Sigal	Tobias	STOBIAS	515.127.4564	2005-07-24	PU_CLERK	2800.0	NULL	114.0	30.0
118	Guy	Himuro	GHIMURO	515.127.4565	2006-11-15	PU_CLERK	2600.0	NULL	114.0	30.0
119	Karen	Colmenares	KCOLMENA	515.127.4566	2007-08-10	PU_CLERK	2500.0	NULL	114.0	30.0
120	Matthew	Weiss	MWEISS	650.123.1234	2004-07-18	ST_MAN	8000.0	NULL	100.0	50.0
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.0	NULL	100.0	50.0
122	Payam	Kaufling	PKAUFLIN	650.123.3234	2003-05-01	ST_MAN	7900.0	NULL	100.0	50.0
123	Shanta	Vollman	SVOLLMAN	650.123.4234	2005-10-10	ST_MAN	6500.0	NULL	100.0	50.0
124	Kevin	Mourgos	KMOURGOS	650.123.5234	2007-11-16	ST_MAN	5800.0	NULL	100.0	50.0
125	Julia	Nayer	JNAYER	650.124.1214	2005-07-16	ST_CLERK	3200.0	NULL	120.0	50.0
126	Irene	Mikkilineni	IMIKKILI	650.124.1224	2006-09-28	ST_CLERK	2700.0	NULL	120.0	50.0
127	James	Landry	JLANDRY	650.124.1334	2007-01-14	ST_CLERK	2400.0	NULL	120.0	50.0
128	Steven	Markle	SMARKLE	650.124.1434	2008-03-08	ST_CLERK	2200.0	NULL	120.0	50.0
129	Laura	Bissot	LBISSOT	650.124.5234	2005-08-20	ST_CLERK	3300.0	NULL	121.0	50.0
130	Mozhe	Atkinson	MATKINSO	650.124.6234	2005-10-30	ST_CLERK	2800.0	NULL	121.0	50.0
131	James	Marlow	JAMRLOW	650.124.7234	2005-02-16	ST_CLERK	2500.0	NULL	121.0	50.0
132	TJ	Olson	TJOLSON	650.124.8234	2007-04-10	ST_CLERK	2100.0	NULL	121.0	50.0
133	Jason	Mallin	JMALLIN	650.127.1934	2004-06-14	ST_CLERK	3300.0	NULL	122.0	50.0
134	Michael	Rogers	MROGERS	650.127.1834	2006-08-26	ST_CLERK	2900.0	NULL	122.0	50.0
135	Ki	Gee	KGEE	650.127.1734	2007-12-12	ST_CLERK	2400.0	NULL	122.0	50.0
136	Hazel	Philtanker	HPHILTAN	650.127.1634	2008-02-06	ST_CLERK	2200.0	NULL	122.0	50.0
137	Renske	Ladwig	RLADWIG	650.121.1234	2003-07-14	ST_CLERK	3600.0	NULL	123.0	50.0
138	Stephen	Stiles	SSTILES	650.121.2034	2005-10-26	ST_CLERK	3200.0	NULL	123.0	50.0
139	John	Seo	JSEO	650.121.2019	2006-02-12	ST_CLERK	2700.0	NULL	123.0	50.0
140	Joshua	Patel	JPATEL	650.121.1834	2006-04-06	ST_CLERK	2500.0	NULL	123.0	50.0
141	Trenna	Rajs	TRAJS	650.121.8009	2003-10-17	ST_CLERK	3500.0	NULL	124.0	50.0
142	Curtis	Davies	CDAVIES	650.121.2994	2005-01-29	ST_CLERK	3100.0	NULL	124.0	50.0
143	Randall	Matos	RMATOS	650.121.2874	2006-03-15	ST_CLERK	2600.0	NULL	124.0	50.0
144	Peter	Vargas	PVARGAS	650.121.2004	2006-07-09	ST_CLERK	2500.0	NULL	124.0	50.0
145	John	Russell	JRUSSEL	011.44.1344.429268	2004-10-01	SA_MAN	14000.0	0.40	100.0	80.0
146	Karen	Partners	KPARTNER	011.44.1344.467268	2005-01-05	SA_MAN	13500.0	0.30	100.0	80.0
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	2005-03-10	SA_MAN	12000.0	0.30	100.0	80.0
148	Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	2007-10-15	SA_MAN	11000.0	0.30	100.0	80.0
149	Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	2008-01-29	SA_MAN	10500.0	0.20	100.0	80.0
150	Peter	Tucker	PTUCKER	011.44.1344.129268	2005-01-30	SA_REP	10000.0	0.30	145.0	80.0
151	David	Bernstein	DBERNSTE	011.44.1344.345268	2005-03-24	SA_REP	9500.0	0.25	145.0	80.0
152	Peter	Hall	PHALL	011.44.1344.478968	2005-08-20	SA_REP	9000.0	0.25	145.0	80.0
153	Christopher	Olsen	COLSEN	011.44.1344.498718	2006-03-30	SA_REP	8000.0	0.20	145.0	80.0
154	Nanette	Cambrault	NCAMBRAU	011.44.1344.987668	2006-12-09	SA_REP	7500.0	0.20	145.0	80.0
155	Oliver	Tuvault	OTUVAULT	011.44.1344.486508	2007-11-23	SA_REP	7000.0	0.15	145.0	80.0
156	Janette	King	JKING	011.44.1345.429268	2004-01-30	SA_REP	10000.0	0.35	146.0	80.0
157	Patrick	Sully	PSULLY	011.44.1345.929268	2004-03-04	SA_REP	9500.0	0.35	146.0	80.0
158	Allan	McEwen	AMCEWEN	011.44.1345.829268	2004-08-01	SA_REP	9000.0	0.35	146.0	80.0
159	Lindsey	Smith	LSMITH	011.44.1345.729268	2005-03-10	SA_REP	8000.0	0.30	146.0	80.0
160	Louise	Doran	LDORAN	011.44.1345.629268	2005-12-15	SA_REP	7500.0	0.30	146.0	80.0
161	Sarath	Sewall	SSEWALL	011.44.1345.529268	2006-11-03	SA_REP	7000.0	0.25	146.0	80.0
162	Clara	Vishney	CVISHNEY	011.44.1346.129268	2005-11-11	SA_REP	10500.0	0.25	147.0	80.0
163	Danielle	Greene	DGREENE	011.44.1346.229268	2007-03-19	SA_REP	9500.0	0.15	147.0	80.0
164	Mattea	Marvins	MMARVINS	011.44.1346.329268	2008-01-24	SA_REP	7200.0	0.10	147.0	80.0
165	David	Lee	DLEE	011.44.1346.529268	2008-02-23	SA_REP	6800.0	0.10	147.0	80.0
166	Sundar	Ande	SANDE	011.44.1346.629268	2008-03-24	SA_REP	6400.0	0.10	147.0	80.0
167	Amit	Banda	ABANDA	011.44.1346.729268	2008-04-21	SA_REP	6200.0	0.10	147.0	80.0
168	Lisa	Ozer	LOZER	011.44.1343.929268	2005-03-11	SA_REP	11500.0	0.25	148.0	80.0
169	Harrison	Bloom	HBLOOM	011.44.1343.829268	2006-03-23	SA_REP	10000.0	0.20	148.0	80.0
170	Tayler	Fox	TFOX	011.44.1343.729268	2006-01-24	SA_REP	9600.0	0.20	148.0	80.0
171	William	Smith	WSMITH	011.44.1343.629268	2007-02-23	SA_REP	7400.0	0.15	148.0	80.0
172	Elizabeth	Bates	EBATES	011.44.1343.529268	2007-03-24	SA_REP	7300.0	0.15	148.0	80.0
173	Sundita	Kumar	SKUMAR	011.44.1343.329268	2008-04-21	SA_REP	6100.0	0.10	148.0	80.0
174	Ellen	Abel	EABEL	011.44.1644.429267	2004-05-11	SA_REP	11000.0	0.30	149.0	80.0
175	Alyssa	Hutton	AHUTTON	011.44.1644.429266	2005-03-19	SA_REP	8800.0	0.25	149.0	80.0
176	Jonathon	Taylor	JTAYLOR	011.44.1644.429265	2006-03-24	SA_REP	8600.0	0.20	149.0	80.0
177	Jack	Livingston	JLIVINGS	011.44.1644.429264	2006-04-23	SA_REP	8400.0	0.20	149.0	80.0
178	Kimberely	Grant	KGRANT	011.44.1644.429263	2007-05-24	SA_REP	7000.0	0.15	149.0	NULL
179	Charles	Johnson	CJOHNSON	011.44.1644.429262	2008-01-04	SA_REP	6200.0	0.10	149.0	80.0
180	Winston	Taylor	WTAYLOR	650.507.9876	2006-01-24	SH_CLERK	3200.0	NULL	120.0	50.0
181	Jean	Fleaur	JFLEAUR	650.507.9877	2006-02-23	SH_CLERK	3100.0	NULL	120.0	50.0
182	Martha	Sullivan	MSULLIVA	650.507.9878	2007-06-21	SH_CLERK	2500.0	NULL	120.0	50.0
183	Girard	Geoni	GGEONI	650.507.9879	2008-02-03	SH_CLERK	2800.0	NULL	120.0	50.0
184	Nandita	Sarchand	NSARCHAN	650.509.1876	2004-01-27	SH_CLERK	4200.0	NULL	121.0	50.0
185	Alexis	Bull	ABULL	650.509.2876	2005-02-20	SH_CLERK	4100.0	NULL	121.0	50.0
186	Julia	Dellinger	JDELLING	650.509.3876	2006-06-24	SH_CLERK	3400.0	NULL	121.0	50.0
187	Anthony	Cabrio	ACABRIO	650.509.4876	2007-02-07	SH_CLERK	3000.0	NULL	121.0	50.0
188	Kelly	Chung	KCHUNG	650.505.1876	2005-06-14	SH_CLERK	3800.0	NULL	122.0	50.0
189	Jennifer	Dilly	JDILLY	650.505.2876	2005-08-13	SH_CLERK	3600.0	NULL	122.0	50.0
190	Timothy	Gates	TGATES	650.505.3876	2006-07-11	SH_CLERK	2900.0	NULL	122.0	50.0
191	Randall	Perkins	RPERKINS	650.505.4876	2007-12-19	SH_CLERK	2500.0	NULL	122.0	50.0
192	Sarah	Bell	SBELL	650.501.1876	2004-02-04	SH_CLERK	4000.0	NULL	123.0	50.0
193	Britney	Everett	BEVERETT	650.501.2876	2005-03-03	SH_CLERK	3900.0	NULL	123.0	50.0
194	Samuel	McCain	SMCCAIN	650.501.3876	2006-07-01	SH_CLERK	3200.0	NULL	123.0	50.0
195	Vance	Jones	VJONES	650.501.4876	2007-03-17	SH_CLERK	2800.0	NULL	123.0	50.0
196	Alana	Walsh	AWALSH	650.507.9811	2006-04-24	SH_CLERK	3100.0	NULL	124.0	50.0
197	Kevin	Feeney	KFEENEY	650.507.9822	2006-05-23	SH_CLERK	3000.0	NULL	124.0	50.0
198	Donald	OConnell	DOCONNEL	650.507.9833	2007-06-21	SH_CLERK	2600.0	NULL	124.0	50.0
199	Douglas	Grant	DGRANT	650.507.9844	2008-01-13	SH_CLERK	2600.0	NULL	124.0	50.0
200	Jennifer	Whalen	JWHALEN	515.123.4444	2003-09-17	AD_ASST	4400.0	NULL	101.0	10.0
201	Michael	Hartstein	MHARTSTE	515.123.5555	2004-02-17	MK_MAN	13000.0	NULL	100.0	20.0
202	Pat	Fay	PFAY	603.123.6666	2005-08-17	MK_REP	6000.0	NULL	201.0	20.0
203	Susan	Mavris	SMAVRIS	515.123.7777	2002-06-07	HR_REP	6500.0	NULL	101.0	40.0
204	Hermann	Baer	HBAER	515.123.8888	2002-06-07	PR_REP	10000.0	NULL	101.0	70.0
205	Shelley	Higgins	SHIGGINS	515.123.8080	2002-06-07	AC_MGR	12008.0	NULL	101.0	110.0
206	William	Gietz	WGIETZ	515.123.8181	2002-06-07	AC_ACCOUNT	8300.0	NULL	205.0	110.0
Total Rows: 107, Total Columns: 11

Table: jobs

The jobs table contains …

Schema: jobs

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'jobs' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
job_id	varchar	10.0	NO	PRI
job_title	varchar	35.0	NO
min_salary	float	NULL	YES
max_salary	float	NULL	YES
Total Rows: 4, Total Columns: 5

Sample data: jobs

run_sql_and_return_html(cnx,f"""
SELECT 

job_id, job_title, min_salary, max_salary 

FROM jobs;
""")

job_id	job_title	min_salary	max_salary
AC_ACCOUNT	Public Accountant	4200.0	9000.0
AC_MGR	Accounting Manager	8200.0	16000.0
AD_ASST	Administration Assistant	3000.0	6000.0
AD_PRES	President	20080.0	40000.0
AD_VP	Administration Vice President	15000.0	30000.0
FI_ACCOUNT	Accountant	4200.0	9000.0
FI_MGR	Finance Manager	8200.0	16000.0
HR_REP	Human Resources Representative	4000.0	9000.0
IT_PROG	Programmer	4000.0	10000.0
MK_MAN	Marketing Manager	9000.0	15000.0
MK_REP	Marketing Representative	4000.0	9000.0
PR_REP	Public Relations Representative	4500.0	10500.0
PU_CLERK	Purchasing Clerk	2500.0	5500.0
PU_MAN	Purchasing Manager	8000.0	15000.0
SA_MAN	Sales Manager	10000.0	20080.0
SA_REP	Sales Representative	6000.0	12008.0
SH_CLERK	Shipping Clerk	2500.0	5500.0
ST_CLERK	Stock Clerk	2008.0	5000.0
ST_MAN	Stock Manager	5500.0	8500.0
Total Rows: 19, Total Columns: 4

Table: job_grades

The job_grades table contains …

Schema: job_grades

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'job_grades' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
grade_level	varchar	3.0	NO	PRI
lowest_sal	float	NULL	YES
highest_sal	float	NULL	YES
Total Rows: 3, Total Columns: 5

Sample data: job_grades

run_sql_and_return_html(cnx,f"""
SELECT 

grade_level, lowest_sal, highest_sal

FROM job_grades;
""")

grade_level	lowest_sal	highest_sal
A	1000.0	2999.0
B	3000.0	5999.0
C	6000.0	9999.0
D	10000.0	14999.0
E	15000.0	24999.0
F	25000.0	40000.0
Total Rows: 6, Total Columns: 3

Table: job_history

The job_history table contains …

Schema: job_history

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'job_history' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
employee_id	int	NULL	NO	PRI
start_date	date	NULL	NO	PRI
end_date	date	NULL	NO	PRI
job_id	varchar	10.0	NO	MUL
department_id	int	NULL	YES	MUL
Total Rows: 5, Total Columns: 5

Sample data: job_history

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, start_date, end_date, job_id, department_id
 
FROM job_history;
""")

employee_id	start_date	end_date	job_id	department_id
101	1997-09-21	2001-10-27	AC_ACCOUNT	110
101	2001-10-28	2005-03-15	AC_MGR	110
102	2001-01-13	2006-07-24	IT_PROG	60
114	2006-03-24	2007-12-31	ST_CLERK	50
122	2007-01-01	2007-12-31	ST_CLERK	50
176	2006-03-24	2006-12-31	SA_REP	80
176	2007-01-01	2007-12-31	SA_MAN	80
200	1995-09-17	2001-06-17	AD_ASST	90
200	2002-07-01	2006-12-31	AC_ACCOUNT	90
201	2004-02-17	2007-12-19	MK_REP	20
Total Rows: 10, Total Columns: 5

Table: locations

The locations table contains …

Schema: locations

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'locations' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
location_id	int	NULL	NO	PRI
street_address	varchar	40.0	YES
postal_code	varchar	12.0	YES
city	varchar	30.0	NO	MUL
state_province	varchar	25.0	YES	MUL
country_id	char	2.0	YES	MUL
Total Rows: 6, Total Columns: 5

Sample data: locations

run_sql_and_return_html(cnx,f"""
SELECT 

location_id, street_address, postal_code, city, state_province, country_id 
 
FROM locations;
""")

location_id	street_address	postal_code	city	state_province	country_id
1000	1297 Via Cola di Rie	00989	Roma	NULL	IT
1100	93091 Calle della Testa	10934	Venice	NULL	IT
1200	2017 Shinjuku-ku	1689	Tokyo	Tokyo Prefecture	JP
1300	9450 Kamiya-cho	6823	Hiroshima	NULL	JP
1400	2014 Jabberwocky Rd	26192	Southlake	Texas	US
1500	2011 Interiors Blvd	99236	South San Francisco	California	US
1600	2007 Zagora St	50090	South Brunswick	New Jersey	US
1700	2004 Charade Rd	98199	Seattle	Washington	US
1800	147 Spadina Ave	M5V 2L7	Toronto	Ontario	CA
1900	6092 Boxwood St	YSW 9T2	Whitehorse	Yukon	CA
2000	40-5-12 Laogianggen	190518	Beijing	NULL	CN
2100	1298 Vileparle (E)	490231	Bombay	Maharashtra	IN
2200	12-98 Victoria Street	2901	Sydney	New South Wales	AU
2300	198 Clementi North	540198	Singapore	NULL	SG
2400	8204 Arthur St	NULL	London	NULL	UK
2500	Magdalen Centre, The Oxford Science Park	OX9 9ZB	Oxford	Oxford	UK
2600	9702 Chester Road	09629850293	Stretford	Manchester	UK
2700	Schwanthalerstr. 7031	80925	Munich	Bavaria	DE
2800	Rua Frei Caneca 1360	01307-002	Sao Paulo	Sao Paulo	BR
2900	20 Rue des Corps-Saints	1730	Geneva	Geneve	CH
3000	Murtenstrasse 921	3095	Bern	BE	CH
3100	Pieter Breughelstraat 837	3029SK	Utrecht	Utrecht	NL
3200	Mariano Escobedo 9991	11932	Mexico City	Distrito Federal,	MX
Total Rows: 23, Total Columns: 6

Table: regions

The regions table contains …

Schema: regions

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'regions' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
region_id	int	NULL	NO	PRI
region_name	varchar	25.0	YES
Total Rows: 2, Total Columns: 5

Sample data: regions

run_sql_and_return_html(cnx,f"""
SELECT 

region_id, region_name
 
FROM regions;
""")

region_id	region_name
1	Europe
2	Americas
3	Asia
4	Middle East and Africa
Total Rows: 4, Total Columns: 2

Table: emp_details_view

The emp_details_view table contains …

Schema: emp_details_view

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'emp_details_view' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_KEY
employee_id	int	NULL	NO
job_id	varchar	10.0	NO
manager_id	int	NULL	YES
department_id	int	NULL	YES
location_id	int	NULL	YES
country_id	char	2.0	YES
first_name	varchar	20.0	YES
last_name	varchar	25.0	NO
salary	decimal	NULL	YES
commission_pct	decimal	NULL	YES
department_name	varchar	30.0	NO
job_title	varchar	35.0	NO
city	varchar	30.0	NO
state_province	varchar	25.0	YES
country_name	varchar	40.0	YES
region_name	varchar	25.0	YES
Total Rows: 16, Total Columns: 5

Sample data: emp_details_view

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name 

FROM

emp_details_view

""")

employee_id	job_id	manager_id	department_id	location_id	country_id	first_name	last_name	salary	commission_pct	department_name	job_title	city	state_province	country_name	region_name
200	AD_ASST	101.0	10	1700	US	Jennifer	Whalen	4400.0	NULL	Administration	Administration Assistant	Seattle	Washington	United States of America	Americas
201	MK_MAN	100.0	20	1800	CA	Michael	Hartstein	13000.0	NULL	Marketing	Marketing Manager	Toronto	Ontario	Canada	Americas
202	MK_REP	201.0	20	1800	CA	Pat	Fay	6000.0	NULL	Marketing	Marketing Representative	Toronto	Ontario	Canada	Americas
114	PU_MAN	100.0	30	1700	US	Den	Raphaely	11000.0	NULL	Purchasing	Purchasing Manager	Seattle	Washington	United States of America	Americas
115	PU_CLERK	114.0	30	1700	US	Alexander	Khoo	3100.0	NULL	Purchasing	Purchasing Clerk	Seattle	Washington	United States of America	Americas
116	PU_CLERK	114.0	30	1700	US	Shelli	Baida	2900.0	NULL	Purchasing	Purchasing Clerk	Seattle	Washington	United States of America	Americas
117	PU_CLERK	114.0	30	1700	US	Sigal	Tobias	2800.0	NULL	Purchasing	Purchasing Clerk	Seattle	Washington	United States of America	Americas
118	PU_CLERK	114.0	30	1700	US	Guy	Himuro	2600.0	NULL	Purchasing	Purchasing Clerk	Seattle	Washington	United States of America	Americas
119	PU_CLERK	114.0	30	1700	US	Karen	Colmenares	2500.0	NULL	Purchasing	Purchasing Clerk	Seattle	Washington	United States of America	Americas
203	HR_REP	101.0	40	2400	UK	Susan	Mavris	6500.0	NULL	Human Resources	Human Resources Representative	London	NULL	United Kingdom	Europe
120	ST_MAN	100.0	50	1500	US	Matthew	Weiss	8000.0	NULL	Shipping	Stock Manager	South San Francisco	California	United States of America	Americas
121	ST_MAN	100.0	50	1500	US	Adam	Fripp	8200.0	NULL	Shipping	Stock Manager	South San Francisco	California	United States of America	Americas
122	ST_MAN	100.0	50	1500	US	Payam	Kaufling	7900.0	NULL	Shipping	Stock Manager	South San Francisco	California	United States of America	Americas
123	ST_MAN	100.0	50	1500	US	Shanta	Vollman	6500.0	NULL	Shipping	Stock Manager	South San Francisco	California	United States of America	Americas
124	ST_MAN	100.0	50	1500	US	Kevin	Mourgos	5800.0	NULL	Shipping	Stock Manager	South San Francisco	California	United States of America	Americas
125	ST_CLERK	120.0	50	1500	US	Julia	Nayer	3200.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
126	ST_CLERK	120.0	50	1500	US	Irene	Mikkilineni	2700.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
127	ST_CLERK	120.0	50	1500	US	James	Landry	2400.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
128	ST_CLERK	120.0	50	1500	US	Steven	Markle	2200.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
129	ST_CLERK	121.0	50	1500	US	Laura	Bissot	3300.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
130	ST_CLERK	121.0	50	1500	US	Mozhe	Atkinson	2800.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
131	ST_CLERK	121.0	50	1500	US	James	Marlow	2500.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
132	ST_CLERK	121.0	50	1500	US	TJ	Olson	2100.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
133	ST_CLERK	122.0	50	1500	US	Jason	Mallin	3300.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
134	ST_CLERK	122.0	50	1500	US	Michael	Rogers	2900.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
135	ST_CLERK	122.0	50	1500	US	Ki	Gee	2400.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
136	ST_CLERK	122.0	50	1500	US	Hazel	Philtanker	2200.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
137	ST_CLERK	123.0	50	1500	US	Renske	Ladwig	3600.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
138	ST_CLERK	123.0	50	1500	US	Stephen	Stiles	3200.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
139	ST_CLERK	123.0	50	1500	US	John	Seo	2700.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
140	ST_CLERK	123.0	50	1500	US	Joshua	Patel	2500.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
141	ST_CLERK	124.0	50	1500	US	Trenna	Rajs	3500.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
142	ST_CLERK	124.0	50	1500	US	Curtis	Davies	3100.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
143	ST_CLERK	124.0	50	1500	US	Randall	Matos	2600.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
144	ST_CLERK	124.0	50	1500	US	Peter	Vargas	2500.0	NULL	Shipping	Stock Clerk	South San Francisco	California	United States of America	Americas
180	SH_CLERK	120.0	50	1500	US	Winston	Taylor	3200.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
181	SH_CLERK	120.0	50	1500	US	Jean	Fleaur	3100.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
182	SH_CLERK	120.0	50	1500	US	Martha	Sullivan	2500.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
183	SH_CLERK	120.0	50	1500	US	Girard	Geoni	2800.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
184	SH_CLERK	121.0	50	1500	US	Nandita	Sarchand	4200.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
185	SH_CLERK	121.0	50	1500	US	Alexis	Bull	4100.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
186	SH_CLERK	121.0	50	1500	US	Julia	Dellinger	3400.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
187	SH_CLERK	121.0	50	1500	US	Anthony	Cabrio	3000.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
188	SH_CLERK	122.0	50	1500	US	Kelly	Chung	3800.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
189	SH_CLERK	122.0	50	1500	US	Jennifer	Dilly	3600.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
190	SH_CLERK	122.0	50	1500	US	Timothy	Gates	2900.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
191	SH_CLERK	122.0	50	1500	US	Randall	Perkins	2500.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
192	SH_CLERK	123.0	50	1500	US	Sarah	Bell	4000.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
193	SH_CLERK	123.0	50	1500	US	Britney	Everett	3900.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
194	SH_CLERK	123.0	50	1500	US	Samuel	McCain	3200.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
195	SH_CLERK	123.0	50	1500	US	Vance	Jones	2800.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
196	SH_CLERK	124.0	50	1500	US	Alana	Walsh	3100.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
197	SH_CLERK	124.0	50	1500	US	Kevin	Feeney	3000.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
198	SH_CLERK	124.0	50	1500	US	Donald	OConnell	2600.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
199	SH_CLERK	124.0	50	1500	US	Douglas	Grant	2600.0	NULL	Shipping	Shipping Clerk	South San Francisco	California	United States of America	Americas
103	IT_PROG	102.0	60	1400	US	Alexander	Hunold	9000.0	NULL	IT	Programmer	Southlake	Texas	United States of America	Americas
104	IT_PROG	103.0	60	1400	US	Bruce	Ernst	6000.0	NULL	IT	Programmer	Southlake	Texas	United States of America	Americas
105	IT_PROG	103.0	60	1400	US	David	Austin	4800.0	NULL	IT	Programmer	Southlake	Texas	United States of America	Americas
106	IT_PROG	103.0	60	1400	US	Valli	Pataballa	4800.0	NULL	IT	Programmer	Southlake	Texas	United States of America	Americas
107	IT_PROG	103.0	60	1400	US	Diana	Lorentz	4200.0	NULL	IT	Programmer	Southlake	Texas	United States of America	Americas
204	PR_REP	101.0	70	2700	DE	Hermann	Baer	10000.0	NULL	Public Relations	Public Relations Representative	Munich	Bavaria	Germany	Europe
145	SA_MAN	100.0	80	2500	UK	John	Russell	14000.0	0.40	Sales	Sales Manager	Oxford	Oxford	United Kingdom	Europe
146	SA_MAN	100.0	80	2500	UK	Karen	Partners	13500.0	0.30	Sales	Sales Manager	Oxford	Oxford	United Kingdom	Europe
147	SA_MAN	100.0	80	2500	UK	Alberto	Errazuriz	12000.0	0.30	Sales	Sales Manager	Oxford	Oxford	United Kingdom	Europe
148	SA_MAN	100.0	80	2500	UK	Gerald	Cambrault	11000.0	0.30	Sales	Sales Manager	Oxford	Oxford	United Kingdom	Europe
149	SA_MAN	100.0	80	2500	UK	Eleni	Zlotkey	10500.0	0.20	Sales	Sales Manager	Oxford	Oxford	United Kingdom	Europe
150	SA_REP	145.0	80	2500	UK	Peter	Tucker	10000.0	0.30	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
151	SA_REP	145.0	80	2500	UK	David	Bernstein	9500.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
152	SA_REP	145.0	80	2500	UK	Peter	Hall	9000.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
153	SA_REP	145.0	80	2500	UK	Christopher	Olsen	8000.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
154	SA_REP	145.0	80	2500	UK	Nanette	Cambrault	7500.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
155	SA_REP	145.0	80	2500	UK	Oliver	Tuvault	7000.0	0.15	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
156	SA_REP	146.0	80	2500	UK	Janette	King	10000.0	0.35	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
157	SA_REP	146.0	80	2500	UK	Patrick	Sully	9500.0	0.35	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
158	SA_REP	146.0	80	2500	UK	Allan	McEwen	9000.0	0.35	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
159	SA_REP	146.0	80	2500	UK	Lindsey	Smith	8000.0	0.30	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
160	SA_REP	146.0	80	2500	UK	Louise	Doran	7500.0	0.30	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
161	SA_REP	146.0	80	2500	UK	Sarath	Sewall	7000.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
162	SA_REP	147.0	80	2500	UK	Clara	Vishney	10500.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
163	SA_REP	147.0	80	2500	UK	Danielle	Greene	9500.0	0.15	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
164	SA_REP	147.0	80	2500	UK	Mattea	Marvins	7200.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
165	SA_REP	147.0	80	2500	UK	David	Lee	6800.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
166	SA_REP	147.0	80	2500	UK	Sundar	Ande	6400.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
167	SA_REP	147.0	80	2500	UK	Amit	Banda	6200.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
168	SA_REP	148.0	80	2500	UK	Lisa	Ozer	11500.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
169	SA_REP	148.0	80	2500	UK	Harrison	Bloom	10000.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
170	SA_REP	148.0	80	2500	UK	Tayler	Fox	9600.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
171	SA_REP	148.0	80	2500	UK	William	Smith	7400.0	0.15	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
172	SA_REP	148.0	80	2500	UK	Elizabeth	Bates	7300.0	0.15	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
173	SA_REP	148.0	80	2500	UK	Sundita	Kumar	6100.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
174	SA_REP	149.0	80	2500	UK	Ellen	Abel	11000.0	0.30	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
175	SA_REP	149.0	80	2500	UK	Alyssa	Hutton	8800.0	0.25	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
176	SA_REP	149.0	80	2500	UK	Jonathon	Taylor	8600.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
177	SA_REP	149.0	80	2500	UK	Jack	Livingston	8400.0	0.20	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
179	SA_REP	149.0	80	2500	UK	Charles	Johnson	6200.0	0.10	Sales	Sales Representative	Oxford	Oxford	United Kingdom	Europe
100	AD_PRES	NULL	90	1700	US	Steven	King	24000.0	NULL	Executive	President	Seattle	Washington	United States of America	Americas
101	AD_VP	100.0	90	1700	US	Neena	Kochhar	17000.0	NULL	Executive	Administration Vice President	Seattle	Washington	United States of America	Americas
102	AD_VP	100.0	90	1700	US	Lex	De Haan	17000.0	NULL	Executive	Administration Vice President	Seattle	Washington	United States of America	Americas
108	FI_MGR	101.0	100	1700	US	Nancy	Greenberg	12008.0	NULL	Finance	Finance Manager	Seattle	Washington	United States of America	Americas
109	FI_ACCOUNT	108.0	100	1700	US	Daniel	Faviet	9000.0	NULL	Finance	Accountant	Seattle	Washington	United States of America	Americas
110	FI_ACCOUNT	108.0	100	1700	US	John	Chen	8200.0	NULL	Finance	Accountant	Seattle	Washington	United States of America	Americas
111	FI_ACCOUNT	108.0	100	1700	US	Ismael	Sciarra	7700.0	NULL	Finance	Accountant	Seattle	Washington	United States of America	Americas
112	FI_ACCOUNT	108.0	100	1700	US	Jose Manuel	Urman	7800.0	NULL	Finance	Accountant	Seattle	Washington	United States of America	Americas
113	FI_ACCOUNT	108.0	100	1700	US	Luis	Popp	6900.0	NULL	Finance	Accountant	Seattle	Washington	United States of America	Americas
205	AC_MGR	101.0	110	1700	US	Shelley	Higgins	12008.0	NULL	Accounting	Accounting Manager	Seattle	Washington	United States of America	Americas
206	AC_ACCOUNT	205.0	110	1700	US	William	Gietz	8300.0	NULL	Accounting	Public Accountant	Seattle	Washington	United States of America	Americas
Total Rows: 106, Total Columns: 16

Reflection

Please add a paragraph or two about how this project went.

At first it was really daunting, all of the installing. I have like computer science PTSD when it comes to making sure everything is installed the right way! It has always been one of the most frusturating parts for no reason! Making sure everything is installed correctly just for it to work is the last thing I should be worrying about! However, it does show a mastery in systems to be able to move through one efficiently and understand why it works the way it does. Having a mastery in

Was it harder or easier than you expected?

It was about as difficult as I expected. Professor said this assigment was going to be slightly trickier, so I took my time. It was relatively difficult, but I have finished all components. Just took a lot of debugging and time.

Did the instructor provide too much information or not enough information?

The instructor provided the perfect information! However, at the end, I still had to install a few more modules, and there was one grammatical error in helpers.py. These things could throw someone off, and it might send them back to the tutorial instead of realizing these errors are quick fixes. Maybe pandas is installed on other systems automatically though.

Do you have suggestions for how it can be improved?

As I said right above. There is one grammatical error in helpers.py and there could be a clarification that the user might need to download separately a few modules. I know we had to avoid using pip earlier, so all of this could be confusing. Otherwise, perfect instruction! The .env could also be mentioned as needing to be edited.

README

Homework 5 - CMSC408 - Spring 2025

This will be the first homework to involve SQL! We are going to installing necessary python packages to get SQL up and running while Quarto converts our code to HTML through VSCode! This will set us up for SQL to end the semester and it will give us the tools to design our own database using SQL!

Homework tasks

Together, we will:

1. Install the necessary python tools and libraries

2. Verify that the tools and libraries are installed.

3. Verify that you can log into the phpMyAdmin site.

4. Verify that your VSCODE can connect to the mySql server.

5. Explore queries and result sets using pandas

6. Explore Quarto as a tool for writing out tables.

7. Explore and document a new database that some provided to you.

8. Render explore.qmd to ensure that everything works.

9. Complete the report.qmd scaffold and render the report!

Files and folders

All necessary files are stored in the ./reports folder.